﻿CREATE PROCEDURE [util].[SetDeleteColumn](
	@DatabaseName sysname,
	@SchemaName sysname,
	@TableName sysname,
	@ColumnName sysname
)
AS
BEGIN
	DECLARE
		@FQColumnName sysname = N'[' + @DatabaseName + N'].[' + @SchemaName + N'].[' + @TableName + N'].[' + @ColumnName + N']'

	DECLARE
		@ColumnExists bit = 0,
		@ColumnExistsQuery nvarchar(2000) = N'
			SELECT
				@ColumnExists = COUNT(*) 
			FROM
				[' + @DatabaseName + N'].INFORMATION_SCHEMA.COLUMNS
			WHERE
				([TABLE_SCHEMA] = ''' + @SchemaName + N''')
			AND ([TABLE_NAME] = ''' + @TableName + N''')
			AND ([COLUMN_NAME] = ''' + @ColumnName + N''')';

	EXEC sp_executesql @ColumnExistsQuery,
		N'@ColumnExists bit OUTPUT',
		@ColumnExists = @ColumnExists OUTPUT;

	IF(@ColumnExists = 0)
		BEGIN
			PRINT @FQColumnName + N' does not exist';
			RETURN;
		END

	DECLARE @FQTableName sysname = '[' + @DatabaseName + N'].[' + @SchemaName + N'].[' + @TableName + N']';

	DECLARE
		@DropDefaultConstraintsDDL nvarchar(512) = 0,
		@ColumnConstraintsQuery nvarchar(2000) = N'
			SET @DropDefaultConstraintsDDL = (
				SELECT N''ALTER TABLE ' + @FQTableName + N' DROP CONSTRAINT ['' + df.[name] + N'']''
				FROM [' + @DatabaseName + N'].sys.columns AS col
				LEFT JOIN [' + @DatabaseName + N'].sys.default_constraints AS df
					ON (df.[parent_object_id] = col.[object_id])
					AND(df.[parent_column_id] = col.[column_id])
				WHERE
					(col.[object_id] = OBJECT_ID(''' + @FQTableName + N'''))
				AND (col.[name] = ''' + @ColumnName + N''')
				FOR XML PATH('''')
			)';
	EXEC dbo.sp_executesql @ColumnConstraintsQuery,
		N'@DropDefaultConstraintsDDL nvarchar(512) OUTPUT',
		@DropDefaultConstraintsDDL = @DropDefaultConstraintsDDL OUTPUT;
	EXEC(@DropDefaultConstraintsDDL);

	DECLARE @DropColumnDDL nvarchar(2000) = N'ALTER TABLE ' + @FQTableName + N' DROP COLUMN [' + @ColumnName + N']';
	EXEC(@DropColumnDDL);
END